package com.ids.dao;

import java.io.UnsupportedEncodingException;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.ids.bean.ForgotPassword;
import com.ids.bean.UserInfo;
import com.ids.util.DbManager;
import com.ids.util.Utlity;

public class LoginDao {

	private static LoginDao instance = new LoginDao();

	private LoginDao() {
	}

	public static LoginDao getInstance() {
		return instance;
	}

	public UserInfo getUser(String empCode, String password ,boolean isEncrypted) {

		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		ResultSet userAuthRS = null;
		ResultSet attempts = null;
		UserInfo userInfo = null;
		String sql = "select firstName,lastName,e.designationId, reportingTo, r.roleName, noOfAttempts "
				+ "from loginInfo l,roles r, employees e "
				+ "where e.empCode = ? and password = ? "
				+ "and l.empCode = e.empCode  and  r.roleId = l.roleId and noOfAttempts < 3";
		try {
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, empCode);
			if(!isEncrypted){
			preStmt.setString(2, Utlity.MD5(password));
			}else{
				preStmt.setString(2, password);	
			}
			userAuthRS = preStmt.executeQuery();
			if (null != userAuthRS && userAuthRS.next()) {
				userInfo = new UserInfo();
				userInfo.setEmpCode(empCode);
				userInfo.setReportingTo(userAuthRS.getInt("reportingTo"));
				userInfo.setDesignationId(userAuthRS.getInt("designationId"));
				userInfo.setRole(userAuthRS.getString("roleName"));
				userInfo.setUserFullName(userAuthRS.getString("firstName")
						+ " " + userAuthRS.getString("lastName"));
				updateLoginAttempts(empCode, 0, conn);
			} else {
				String sqlQuery = "select noOfAttempts from logininfo where empCode= ?";
				try {
					PreparedStatement prepStmt = conn
							.prepareStatement(sqlQuery);
					prepStmt.setString(1, empCode);
					attempts = prepStmt.executeQuery();
					if (attempts.next()) {
						int noOfAttempt = attempts.getInt("noOfAttempts") + 1;
						updateLoginAttempts(empCode, noOfAttempt, conn);
					}
				} catch (Exception e) {
					e.printStackTrace();
					System.err
							.println("Got an exception while returning a database! ");
					System.err.println(e.getMessage());
				} finally {
					DbManager.closeResultSet(attempts);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbManager.closeResultSet(userAuthRS);
			DbManager.closeConnection(conn);
		}
		return userInfo;
	}

	public void updateLoginAttempts(String empCode, int noOfAttempts,
			Connection conn) {
		String sqlUpdate = "update loginInfo set noOfAttempts = ? where empCode=?";
		try {
			PreparedStatement preStmt = conn.prepareStatement(sqlUpdate);
			preStmt.setInt(1, noOfAttempts);
			preStmt.setString(2, empCode);
			preStmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("Got an exception while returning a database! ");
			System.err.println(e.getMessage());
		} finally {
			DbManager.closeConnection(conn);
		}
	}

	public boolean updatePassword(String empCode, String password,
			String newPassword) {
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		String sqlUpdate = "update loginInfo set password = ? where empCode=? and password = ?";
		boolean ret = false;
		try {
			PreparedStatement preStmt = conn.prepareStatement(sqlUpdate);
			preStmt.setString(1, Utlity.MD5(newPassword));
			preStmt.setString(2, empCode);
			preStmt.setString(3, Utlity.MD5(password));
			int count = preStmt.executeUpdate();
			if (count >= 1) {
				ret = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.err
					.println("Got an exception while returning a database! in LoginDao.updatePassword()");
			System.err.println(e.getMessage());
		} finally {
			DbManager.closeConnection(conn);
		}
		return ret;
	}

	public static void main(String[] args) throws SQLException {
		LoginDao s = new LoginDao();
		s.getPrimaryMail("11");
	}

	private boolean isEmployeeExists(String empid) throws SQLException {
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		boolean isEmployeeExists = false;
		ResultSet rs = null;
		String sqlCheck = "select * from logininfo where empCode=?";
		try {
			PreparedStatement preStmt = conn.prepareStatement(sqlCheck);
			preStmt.setString(1, empid);
			rs = preStmt.executeQuery();
			if (rs.next()) {
				isEmployeeExists = true;
			} else {
				isEmployeeExists = false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			DbManager.closeConnection(conn);
		}
		return isEmployeeExists;
	}

	public ForgotPassword forgetPassword(String empid) throws SQLException {
		String key = "";
		ForgotPassword fp = new ForgotPassword();
		if (isEmployeeExists(empid)) {
			try {
				key = (Utlity.MD5(empid + "testing"));
				fp.setStatus(true);
				fp.setKey(key);
				fp.setEmpCode(empid);
				fp.setTo(getPrimaryMail(empid));
				fp.setEmail(getPrimaryMail(empid));
			} catch (NoSuchAlgorithmException e) {
				e.printStackTrace();
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}
			DbManager dbManager = new DbManager();
			Connection conn = dbManager.getConnection();
			String sqlInsert = "insert into forgotpassword values(?,?,?,?)";
			try {
				PreparedStatement preStmt = conn.prepareStatement(sqlInsert);
				preStmt.setString(1, empid);
				preStmt.setString(2, key);
				Date currentDate = new Date(new java.util.Date().getTime());
				preStmt.setDate(3, currentDate);
				preStmt.setString(4, "1");
				preStmt.execute();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DbManager.closeConnection(conn);
			}
		}
		return fp;
	}

	public String getPrimaryMail(String empid) {
		String primaryMail = "";
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		String sqlSelect = "select primaryEmail from employees where empCode=?";
		try {
			PreparedStatement preStmt = conn.prepareStatement(sqlSelect);
			preStmt.setString(1, empid);
			ResultSet rs = preStmt.executeQuery();
			if (rs.next()) {
				primaryMail = rs.getString(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbManager.closeConnection(conn);
		}
		System.out.println(primaryMail);
		return primaryMail;
	}

	public String sendPasswordLink(String empid, String primaryMail) {
		return "";
	}

	public String recoverPassword(String newPassword, String confirmPassword,
		String empCode, String key) throws Exception {
		String status = "error";
		ResultSet rs = null;
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		if (newPassword.equals(confirmPassword)) {
			String sqlCorrectEmployeeQuery = "select empCode from forgotpassword where  empCode = ? and keyCode = ? and isExpired=1";
			try {
				PreparedStatement prepStmt = conn.prepareStatement(sqlCorrectEmployeeQuery);
				prepStmt.setString(1, empCode);
				prepStmt.setString(2, key);
				rs = prepStmt.executeQuery();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (rs.next()) {
			String sqlChangePassword = "update loginInfo set password = ? where empCode=?";
			try {
				PreparedStatement preStmt = conn.prepareStatement(sqlChangePassword);
				preStmt.setString(1, Utlity.MD5(confirmPassword));
				preStmt.setString(2, empCode);
				preStmt.executeUpdate();
				status = "success";
				System.out.println("chk6");
			} catch (Exception e) {
				e.printStackTrace();
			}
			updateExpired1(empCode);
			System.out.println("chk7");
		}
		return status;
	}

	public String updateExpired1(String empCode) {
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		String sqlChangePassword = "delete from forgotpassword where empCode=?";
		try {
			PreparedStatement preStmt = conn.prepareStatement(sqlChangePassword);
			preStmt.setString(1, empCode);
			preStmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}

}
